from typing import Optional, List

from pydantic import BaseModel, Field
from sqlalchemy import Boolean, Date, DateTime, Index, Integer, Numeric, PrimaryKeyConstraint, String, Text, \
    UniqueConstraint, text, and_, ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, foreign
import datetime
import decimal

from datebase import Base


# back_populates 是对应属性名，不是表名

class Customers(Base):
    __tablename__ = 'customers'
    __table_args__ = (
        PrimaryKeyConstraint('customer_id', name='customers_pkey'),
        UniqueConstraint('email', name='customers_email_key'),
        {'schema': 'test_two'}
    )

    customer_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    email: Mapped[str] = mapped_column(String(100))
    registration_date: Mapped[datetime.date] = mapped_column(Date)
    phone: Mapped[Optional[str]] = mapped_column(String(20))
    address: Mapped[Optional[str]] = mapped_column(Text)
    status: Mapped[Optional[str]] = mapped_column(String(20), server_default=text("'active'::character varying"))
    is_deleted: Mapped[Optional[bool]] = mapped_column(Boolean, server_default=text('false'))

    orders: Mapped[List["Orders"]] = relationship(
        "Orders",
        primaryjoin="Customers.customer_id == foreign(Orders.customer_id)",
        back_populates="customer",
        viewonly=True
    )

    payments: Mapped[List["Payments"]] = relationship(
        "Payments",
        primaryjoin="Customers.customer_id == foreign(Payments.customer_id)",
        back_populates="customer",
        viewonly=True
    )


class Orders(Base):
    __tablename__ = 'orders'
    __table_args__ = (
        PrimaryKeyConstraint('order_id', name='orders_pkey'),
        Index('idx_orders_customer_id', 'customer_id'),
        {'schema': 'test_two'}
    )

    order_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    customer_id: Mapped[int] = mapped_column(Integer)  # 无 ForeignKey
    order_date: Mapped[datetime.datetime] = mapped_column(DateTime)
    total_amount: Mapped[decimal.Decimal] = mapped_column(Numeric(10, 2))
    shipping_address: Mapped[str] = mapped_column(Text)
    order_status: Mapped[Optional[str]] = mapped_column(String(20),
                                                        server_default=text("'processing'::character varying"))
    is_deleted: Mapped[Optional[bool]] = mapped_column(Boolean, server_default=text('false'))

    customer: Mapped["Customers"] = relationship(
        "Customers",
        primaryjoin="foreign(Orders.customer_id) == Customers.customer_id",
        back_populates="orders",
        remote_side="Customers.customer_id"
    )


class Payments(Base):
    __tablename__ = 'payments'
    __table_args__ = (
        PrimaryKeyConstraint('payment_id', name='payments_pkey'),
        Index('idx_payments_customer_id', 'customer_id'),
        {'schema': 'test_two'}
    )

    payment_id: Mapped[int] = mapped_column(Integer, primary_key=True)
    customer_id: Mapped[int] = mapped_column(Integer)  # 无 ForeignKey
    payment_date: Mapped[datetime.datetime] = mapped_column(DateTime)
    amount: Mapped[decimal.Decimal] = mapped_column(Numeric(10, 2))
    payment_method: Mapped[str] = mapped_column(String(50))
    transaction_id: Mapped[Optional[str]] = mapped_column(String(100))
    status: Mapped[Optional[str]] = mapped_column(String(20), server_default=text("'completed'::character varying"))
    is_deleted: Mapped[Optional[bool]] = mapped_column(Boolean, server_default=text('false'))

    customer: Mapped["Customers"] = relationship(
        "Customers",
        primaryjoin="foreign(Payments.customer_id) == Customers.customer_id",
        back_populates="payments",
        remote_side="Customers.customer_id"
    )


class OrdersCreate(BaseModel):
    """订单信息"""
    order_id: Optional[int] = Field(
        default=None,
        description="订单唯一标识ID"
    )
    customer_id: Optional[int] = Field(
        default=None,
        description="关联的顾客ID"
    )
    order_date: Optional[datetime.datetime] = Field(
        default=None,
        description="订单创建时间（ISO 8601格式）"
    )
    total_amount: Optional[decimal.Decimal] = Field(
        default=None,
        description="订单总金额（保留两位小数）"
    )
    shipping_address: Optional[str] = Field(
        default=None,
        description="商品配送地址"
    )
    order_status: Optional[str] = Field(
        default=None,
        description="订单状态：pending/paid/shipped/completed"
    )
    is_deleted: Optional[bool] = Field(
        default=None,
        description="软删除标记（true表示已删除）"
    )


class PaymentsCreate(BaseModel):
    """支付信息"""
    payment_id: Optional[int] = Field(
        default=None,
        description="支付记录唯一ID"
    )
    customer_id: Optional[int] = Field(
        default=None,
        description="关联的顾客ID"
    )
    payment_date: Optional[datetime.datetime] = Field(
        default=None,
        description="支付完成时间（ISO 8601格式）"
    )
    amount: Optional[decimal.Decimal] = Field(
        default=None,
        description="实际支付金额（人民币）"
    )
    payment_method: Optional[str] = Field(
        default=None,
        description="支付方式：wechat/alipay/unionpay/credit_card"
    )
    transaction_id: Optional[str] = Field(
        default=None,
        description="第三方支付平台交易号"
    )
    status: Optional[str] = Field(
        default=None,
        description="支付状态：pending/success/failed"
    )
    is_deleted: Optional[bool] = Field(
        default=None,
        description="软删除标记（true表示已删除）"
    )


class CustomersCreate(BaseModel):
    """顾客创建请求体（对应图片中的JSON结构）"""
    customer_id: Optional[int] = Field(
        default=None,
        description="顾客唯一标识ID（0表示由系统生成）"
    )
    name: Optional[str] = Field(
        default=None,
        description="顾客真实姓名（2-50个字符）",
        min_length=2,
        max_length=50
    )
    email: Optional[str] = Field(
        default=None,
        description="电子邮箱（需符合邮箱格式验证）"
    )
    registration_date: Optional[datetime.date] = Field(
        default=None,
        description="账户注册日期（YYYY-MM-DD格式）"
    )
    phone: Optional[str] = Field(
        default=None,
        description="手机号码（11位数字）"
        # regex=r"^\d{11}$"
    )
    address: Optional[str] = Field(
        default=None,
        description="详细联系地址"
    )
    status: Optional[str] = Field(
        default=None,
        description="账户状态：active/inactive/banned"
    )
    is_deleted: Optional[bool] = Field(
        default=None,
        description="软删除标记（true表示已删除）"
    )
    orders: Optional[List[OrdersCreate]] = Field(
        default=None,
        description="关联的订单列表"
    )
    payments: Optional[List[PaymentsCreate]] = Field(
        default=None,
        description="关联的支付记录"
    )
